package com.system.demo.utils;

import com.system.demo.entity.DTO.NewStudentDTO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;


public class ExcelExportUtil {

    private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);

    public static <T> void exportToExcel(List<T> data, OutputStream outputStream) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Data");

        // 获取类的第一个实例的所有字段名，假设所有对象都是同一个类型
        if (data.isEmpty()) {
            logger.warn("Data list is empty, no columns to write.");
            return;
        }

        T firstObj = data.get(0);
        Field[] fields = firstObj.getClass().getDeclaredFields();

        // 创建表头
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < fields.length; i++) {
            Cell headerCell = headerRow.createCell(i);
            setCellValue(headerCell, fields[i].getName());
        }

        // 填充数据
        int rowNum = 1;
        for (T obj : data) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                field.setAccessible(true);
                try {
                    Object value = field.get(obj);
                    Cell cell = row.createCell(i);
                    setCellValue(cell, value);
                } catch (IllegalAccessException e) {
                    logger.warn("Field " + field.getName() + " not accessible in object " + obj);
                    setCellValue(row.createCell(i), ""); // 设置为空字符串
                }
            }
        }

        // 输出到流
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            logger.error("Failed to write Excel file to output stream", e);
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                logger.error("Failed to close workbook", e);
            }
        }
    }


    public static void setCellValue(Cell cell, Object value) {
        if (cell == null) {
            throw new IllegalArgumentException("Cell cannot be null");
        }
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Integer) {
            cell.setCellValue(((Integer) value).doubleValue());
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof LocalDate) {
            cell.setCellValue(((LocalDate) value).toString());
        } else if (value instanceof LocalDateTime) {
            cell.setCellValue(((LocalDateTime) value).toString());
        } else {
            cell.setCellValue(value.toString());
        }
    }


    //创建一个工具类来读取Excel文件  批量添加student以及对应的user账户
    public static List<NewStudentDTO> readExcel(InputStream inputStream) throws Exception {
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rows = sheet.iterator();

        List<NewStudentDTO> students = new ArrayList<>();
        if (rows.hasNext()) {
            rows.next(); // 跳过标题行
        }

        while (rows.hasNext()) {
            Row currentRow = rows.next();
            NewStudentDTO student = new NewStudentDTO();

            // 读取stuClass
            Cell stuClassCell = currentRow.getCell(0);
            student.setStuClass(getCellValueAsString(stuClassCell));

            // 读取uid
            Cell uidCell = currentRow.getCell(1);
            student.setUid(getCellValueAsString(uidCell));

            // 读取stuName
            Cell stuNameCell = currentRow.getCell(2);
            student.setStuName(getCellValueAsString(stuNameCell));

            // 读取gender
            Cell genderCell = currentRow.getCell(3);
            student.setGender(getCellValueAsString(genderCell));

            // 读取id
            Cell idCell = currentRow.getCell(4);
            student.setId(getCellValueAsString(idCell));

            students.add(student);
        }

        workbook.close();
        return students;
    }

    private static String getCellValueAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}